package com.quanxi.qxaimain.controller.v1.forVueController;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.fastjson.JSONObject;
import com.quanxi.qxaimain.common.globalException.ResponseVo;
import com.quanxi.qxaimain.common.globalException.ResultCode;
import com.quanxi.qxaimain.common.message.ymlConfig;
import com.quanxi.qxaimain.common.util.sqlFormat.sqlFormat;
import com.quanxi.qxaimain.dao.forVue.DMLMapper;
import com.quanxi.qxaimain.dao.indicators.indicatorsDataMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author lyx
 * @create 2023/4/18 16:19
 * 表数据更改更新指标
 */



@Slf4j
@RestController
@RequestMapping("/updateIndicator")
@Api(tags = "表数据更改更新指标")
public class updateIndicatorController {

    @Resource
    indicatorsDataMapper indicatorsDataMapper;
    @Resource
    DMLMapper dmlMapper;
    @Resource
    private ymlConfig ymlCofig;



    @ApiOperation(value = "获取指标对应的表",  notes = "")
    @ApiImplicitParams({
    @ApiImplicitParam(name = "ind_id", value = "指标唯一编码", required = true, dataType = "String")})
    @PostMapping("/getTableName")
    public Object getTableName(@RequestParam String ind_id){

        if(ind_id==null||ind_id.length()==0){
            return  new ResponseVo(ResultCode.VALIDATE_ERROR.getCode(),ResultCode.VALIDATE_ERROR.getMsg(),"ind_id为空");
        }


        JSONObject jsonObject=new JSONObject();
        List<Map<String, Object>> sqllist = indicatorsDataMapper.find_indicators_sql(ind_id);//参数为空则查询全部
        // 单sql和明细sql 都为空
        if (sqllist.size()==0){
            jsonObject.put("sql_total_tableNames","");
            jsonObject.put("sql_detail_tableNames","");
            return  jsonObject;
        }

        List<String> sql_total_tableNames=new ArrayList<>();
        List<String> sql_total_tableNames_chinese=new ArrayList<>();

        List<String> sql_detail_tableNames=new ArrayList<>();

        List<String> sql_detail_tableNames_chinese=new ArrayList<>();


        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String D =simpleDateFormat.format(date);


        String sql_total = sqllist.get(0).get("sql_total").toString();
        String sql_detail =sqllist.get(0).get("sql_detail").toString();
        int detail_flag  =(Integer) sqllist.get(0).get("detail_flag");// 0为sql语句 1为存储过程名称


        if (sql_total.length()>0){
            String sqlformat= sqlFormat.sqlFormat(sql_total,D,D,"");
            sql_total_tableNames= getTableNameBySql(sqlformat);
            if (sql_total_tableNames.size()>0){
            List<Map<String, Object>> searchtablename = dmlMapper.searchtablename(sql_total_tableNames,ymlCofig.getUsername().toUpperCase());

                for (int i = 0; i <searchtablename.size() ; i++) {
                    sql_total_tableNames_chinese.add(searchtablename.get(i).get("TABLE_NAME_CHINESE").toString());
                }
            }

        }else {
           sql_total_tableNames= null;
            sql_total_tableNames_chinese=null;
        }

        if (sql_detail.length()>0){
                if (detail_flag==0){
                String sqlformat= sqlFormat.sqlFormat(sql_detail,D,D,"");
                sql_detail_tableNames= getTableNameBySql(sqlformat);
                    if (sql_detail_tableNames.size()>0){
                    List<Map<String, Object>> searchtablename = dmlMapper.searchtablename(sql_detail_tableNames,ymlCofig.getUsername().toUpperCase());
                        for (int i = 0; i <searchtablename.size() ; i++) {
                            sql_detail_tableNames_chinese .add(searchtablename.get(i).get("TABLE_NAME_CHINESE").toString());
                        }
                    }

                }
                if (detail_flag==1){
                  sql_detail_tableNames=Arrays.asList("该指标为存储过程"+sql_detail+";无法识别表");
                 sql_detail_tableNames_chinese=Arrays.asList("该指标为存储过程"+sql_detail+";无法识别表");
                }
            }
        else {
                sql_detail_tableNames= null;
            sql_detail_tableNames_chinese=null;
            }

        jsonObject.put("sql_total_tableNames",sql_total_tableNames);
        jsonObject.put("sql_total_tableNames_chinese",sql_total_tableNames_chinese);
        jsonObject.put("sql_detail_tableNames",sql_detail_tableNames);
        jsonObject.put("sql_detail_tableNames_chinese",sql_detail_tableNames_chinese);

        return jsonObject ;
    }

   /**
    * oracle 解析sql表
    *
    * */
    public  static List<String> getTableNameBySql(String sql) {
        //String dbType = JdbcConstants.MySql; // mysql
        String dbType = JdbcConstants.ORACLE; // ORACLE
        try {
            List<String> tableNameList = new ArrayList<>();
            //格式化输出
            String sqlResult = SQLUtils.format(sql, dbType);
            //log.info("格式化后的sql:[{}]",sqlResult);

            List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
            if (CollectionUtils.isEmpty(stmtList)) {
            //    log.info("stmtList为空无需获取");
                return Collections.emptyList();
            }
            for (SQLStatement sqlStatement : stmtList) {
                //  MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();// mysql
                OracleSchemaStatVisitor oracleSchemaStatVisitor = new OracleSchemaStatVisitor();// ORACLE
                sqlStatement.accept(oracleSchemaStatVisitor);
                Map<TableStat.Name, TableStat> tables = oracleSchemaStatVisitor.getTables();
             //   log.info("druid解析sql的结果集:[{}]",tables);
                Set<TableStat.Name> tableNameSet = tables.keySet();
                for (TableStat.Name name : tableNameSet) {
                    String tableName = name.getName();
                    // 去掉别名
                      tableName = tableName.substring(  tableName.indexOf('.')+1,tableName.length()).toUpperCase();
                    if (StringUtils.isNotBlank(tableName)) {

                        tableNameList.add(tableName);
                    }
                }
            }
          //  log.info("解析sql后的表名:[{}]",tableNameList);
            return tableNameList;
        } catch (Exception e) {
          //  log.info("**************异常SQL:[{}]*****************\\n",sql);
          //  log.info(e.getMessage(),e);
        }
        return Collections.emptyList();
    }



}